/**
 * 
 */
package org.mhz.filetodb.main;

import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 * @author Mahan
 * 
 * File to DB class
 */
public class FileToDB
{
    public static void main(String[] args)
    {    	    	
        Connection con = null;
        Statement st = null;
        FileInputStream fstream = null;
        DataInputStream in = null;
        BufferedReader br = null;
        int lastEnteredId = 0;
        
        try
        {
        	if ((args != null) && (args.length != 2))
        	{
        		throw new Exception("There should be at least 2 arguments:\n 1) Location of file \n 2) Table name");
        	}
        	
        	final String fileLocation = args[0];
        	final String dbTable = args[1];
        	
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/oceandb", "root", "password");
            st = con.createStatement();

            // Get the last entered id
            final String lastEnteredIdSQL = "SELECT max(id) FROM readable_content_last_entries_v";
            final ResultSet resultSet = st.executeQuery(lastEnteredIdSQL);
            
            if (resultSet != null)
            {
	            // Fetch each row from the result set
	            while (resultSet.next()) 
	            {
	                lastEnteredId = resultSet.getInt(1);
	            }
            }
            
            fstream = new FileInputStream(fileLocation);
            in = new DataInputStream(fstream);
            br = new BufferedReader(new InputStreamReader(in));
            
            String strLine;
            
            final ArrayList<String> list = new ArrayList<String>();
            
            while ((strLine = br.readLine()) != null)
            {
            	final String trimmedLine = strLine.trim();
            	if (!trimmedLine.isEmpty())
            	{
                    list.add(trimmedLine);
            	}
            }
                      
            String lineToInsert = "";
            int i = 0;
            for (String line : list)
            {
            	// increment the last entered id
            	lastEnteredId++;
            	
                // escape single quotes
            	lineToInsert = line.replaceAll("\'", "\\\\\'");

                final String sql = "insert into " + dbTable + " (id, content, row_created_dttm) values (" + lastEnteredId + ", '" + lineToInsert + "', CURRENT_TIMESTAMP)";
                System.out.println(i + " - SQL >" + sql + "<");
                final int k = st.executeUpdate(sql);
                
                i++;
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            try
            {
                if (br != null)
                {
                    br.close();
                }
            
                if (in != null)
                {
                    in.close();
                }
                
                if (fstream != null)
                {
                    fstream.close();
                }                
                
                if (st != null)
                {
                    st.close();
                }
                
                if (con != null)
                {
                    con.close();
                }                
            }
            catch (IOException ioEx)
            {
                ioEx.printStackTrace();
            }
            catch (SQLException sqlEx)
            {
                sqlEx.printStackTrace();
            }             
        }
    }
}
